
libname umetrics "/university_employee_usiris/2018";
libname lehdt13 "~/mixed/lehd_interleave_t13/2018";
libname lehdt26 "~/mixed/lehd_interleave/2018";
libname outdir "projectdir/data/raw_pulls/lehd_usiris";
libname opm "~/mixed/lehd_s2014_opm/2014";

**********************************************************************************************************;
**********************************************************************************************************;
* Import the UMETRICS employeeid-PIK crosswalk, de-dup, only keep non-missing PIKs, and sort by PIK; 
*    These PIKS will be linked to the LEHD/OPM PHF;
PROC SQL;
  CREATE TABLE work.umetrics_piks AS
  SELECT DISTINCT pik
  FROM umetrics.usiris_emnmxwalk_2018q4a_2018q4a
  WHERE pik IS NOT MISSING;
QUIT;
PROC SORT DATA=work.umetrics_piks; BY pik; RUN;
**********************************************************************************************************;
**********************************************************************************************************;



**********************************************************************************************************;
**********************************************************************************************************;

* Link the UMETRICS PIKs to LEHD Interleave PHF;
DATA outdir.phf_interleave_b_usiris;
  MERGE lehdt13.phf_interleave_b (IN=A) work.umetrics_piks (IN=B);
  BY pik;
  IF A=1 & B=1 THEN _merge=3;
  IF A=1 & B=0 THEN _merge=1;
  IF A=0 & B=1 THEN _merge=2;
  IF _merge=1 THEN DELETE;
  IF _merge=3 THEN flag_pikin_phf=1;
  IF _merge=2 THEN flag_pikin_phf=0;
  DROP _merge;
RUN;

PROC EXPORT
  DATA = outdir.phf_interleave_b_usiris
  OUTFILE = "projectdir/data/raw_pulls/lehd_usiris/phf_interleave_b_2018_usiris_2018q4.dta"
  DBMS = DTA
  REPLACE;
RUN;

**********************************************************************************************************;
**********************************************************************************************************;


**********************************************************************************************************;
**********************************************************************************************************;

DATA outdir.opm_us_ehf_phf_usiris;
  MERGE opm.opm_us_ehf_phf (IN=A) work.umetrics_piks (IN=B);
  BY pik;
  IF A=1 & B=1 THEN _merge=3;
  IF A=1 & B=0 THEN _merge=1;
  IF A=0 & B=1 THEN _merge=2;
  IF _merge=1 THEN DELETE;
  IF _merge=3 THEN flag_pikin_opmehfphf=1;
  IF _merge=2 THEN flag_pikin_opmehfphf=0;
  DROP _merge;
RUN;

PROC EXPORT
  DATA = outdir.opm_us_ehf_phf_usiris
  OUTFILE = "projectdir/data/raw_pulls/lehd_usiris/opm_us_ehf_phf_2014_usiris_2018q4.dta"
  DBMS = DTA
  REPLACE;
RUN;

**********************************************************************************************************;
**********************************************************************************************************;




**********************************************************************************************************;
**********************************************************************************************************;

%macro seinunit_num(num);

  PROC SQL;
    CREATE TABLE work.sein_seinunit&num._iris AS
    SELECT DISTINCT sein, seinunit&num AS seinunit
    FROM outdir.phf_interleave_b_usiris
  QUIT;

%mend;

%seinunit_num(1);
%seinunit_num(2);
%seinunit_num(3);
%seinunit_num(4);
%seinunit_num(5);
%seinunit_num(6);
%seinunit_num(7);
%seinunit_num(8);
%seinunit_num(9);
%seinunit_num(10);

DATA work.sein_seinunit; 
  SET sein_seinunit1_iris
      sein_seinunit2_iris
      sein_seinunit3_iris
      sein_seinunit4_iris
      sein_seinunit5_iris
      sein_seinunit6_iris
      sein_seinunit7_iris
      sein_seinunit8_iris
      sein_seinunit9_iris
      sein_seinunit10_iris;
RUN;


PROC SQL;
  CREATE TABLE work.sein_seinunit_small AS
  SELECT DISTINCT sein, seinunit
  FROM work.sein_seinunit
  WHERE (sein IS NOT MISSING) AND (seinunit IS NOT MISSING);
QUIT;

PROC SQL;
  CREATE TABLE work.sein_small AS
  SELECT DISTINCT sein
  FROM work.sein_seinunit
  WHERE sein IS NOT MISSING;
QUIT;

**********************************************************************************************************;
**********************************************************************************************************;





**********************************************************************************************************;
**********************************************************************************************************;
* Create SEIN-SEINUNIT-level file;

DATA outdir.match;
  MERGE lehdt13.ecf_interleave_seinunit_t13 (IN=A) work.sein_seinunit_small (IN=B);
  BY sein seinunit;
  IF A=1 & B=1 THEN _merge=3;
  IF A=1 & B=0 THEN _merge=1;
  IF A=0 & B=1 THEN _merge=2;
  IF _merge=1 THEN DELETE;
  IF _merge=3 THEN flag_seinunitin_ecf=1;
  IF _merge=2 THEN flag_seinunitin_ecf=0;
  DROP _merge;
RUN;

PROC EXPORT
  DATA = outdir.match
  OUTFILE = "projectdir/data/raw_pulls/lehd_usiris/ecf_interleave_seinunit_t13_2018_usiris_2018q4.dta"
  DBMS = DTA
  REPLACE;
RUN;


DATA outdir.match;
  MERGE lehdt26.ecf_interleave_seinunit_t26 (IN=A) work.sein_seinunit_small (IN=B);
  BY sein seinunit;
  IF A=1 & B=1 THEN _merge=3;
  IF A=1 & B=0 THEN _merge=1;
  IF A=0 & B=1 THEN _merge=2;
  IF _merge=1 THEN DELETE;
  IF _merge=3 THEN flag_seinunitin_ecf=1;
  IF _merge=2 THEN flag_seinunitin_ecf=0;
  DROP _merge;
RUN;

PROC EXPORT
  DATA = outdir.match
  OUTFILE = "projectdir/data/raw_pulls/lehd_usiris/ecf_interleave_seinunit_t26_2018_usiris_2018q4.dta"
  DBMS = DTA
  REPLACE;
RUN;
**********************************************************************************************************;
**********************************************************************************************************;






**********************************************************************************************************;
**********************************************************************************************************;
* Create SEIN-level file;

DATA outdir.match;
  MERGE lehdt13.ecf_interleave_sein_t13 (IN=A) work.sein_small (IN=B);
  BY sein;
  IF A=1 & B=1 THEN _merge=3;
  IF A=1 & B=0 THEN _merge=1;
  IF A=0 & B=1 THEN _merge=2;
  IF _merge=1 THEN DELETE;
  IF _merge=3 THEN flag_seinin_ecf=1;
  IF _merge=2 THEN flag_seinin_ecf=0;
  DROP _merge;
RUN;

PROC EXPORT
  DATA = outdir.match
  OUTFILE = "projectdir/data/raw_pulls/lehd_usiris/ecf_interleave_sein_t13_2018_usiris_2018q4.dta"
  DBMS = DTA
  REPLACE;
RUN;


DATA outdir.match;
  MERGE lehdt26.ecf_interleave_sein_t26 (IN=A) work.sein_small (IN=B);
  BY sein;
  IF A=1 & B=1 THEN _merge=3;
  IF A=1 & B=0 THEN _merge=1;
  IF A=0 & B=1 THEN _merge=2;
  IF _merge=1 THEN DELETE;
  IF _merge=3 THEN flag_seinin_ecf=1;
  IF _merge=2 THEN flag_seinin_ecf=0;
  DROP _merge;
RUN;

PROC EXPORT
  DATA = outdir.match
  OUTFILE = "projectdir/data/raw_pulls/lehd_usiris/ecf_interleave_sein_t26_2018_usiris_2018q4.dta"
  DBMS = DTA
  REPLACE;
RUN;
**********************************************************************************************************;
**********************************************************************************************************;

















































**********************************************************************************************************;
**********************************************************************************************************;
* Create SEIN-SEINUNIT-level file;
PROC SQL;
  CREATE TABLE work.sein_seinunit_iris AS
  SELECT DISTINCT sein, seinunit1 AS seinunit
  FROM outdir.phf_interleave_b_usiris
  WHERE sein IS NOT MISSING;
QUIT;

DATA outdir.match;
  MERGE lehdt13.ecf_interleave_seinunit_t13 (IN=A) work.sein_seinunit_iris (IN=B);
  BY sein seinunit;
  IF A=1 & B=1 THEN _merge=3;
  IF A=1 & B=0 THEN _merge=1;
  IF A=0 & B=1 THEN _merge=2;
  IF _merge=1 THEN DELETE;
  IF _merge=3 THEN flag_seinunitin_ecf=1;
  IF _merge=2 THEN flag_seinunitin_ecf=0;
  DROP _merge;
RUN;

PROC EXPORT
  DATA = outdir.match
  OUTFILE = "projectdir/data/raw_pulls/lehd_usiris/ecf_interleave_seinunit_t13_2018_usiris_2018q4.dta"
  DBMS = DTA
  REPLACE;
RUN;


DATA outdir.match;
  MERGE lehdt26.ecf_interleave_seinunit_t26 (IN=A) work.sein_seinunit_iris (IN=B);
  BY sein seinunit;
  IF A=1 & B=1 THEN _merge=3;
  IF A=1 & B=0 THEN _merge=1;
  IF A=0 & B=1 THEN _merge=2;
  IF _merge=1 THEN DELETE;
  IF _merge=3 THEN flag_seinunitin_ecf=1;
  IF _merge=2 THEN flag_seinunitin_ecf=0;
  DROP _merge;
RUN;

PROC EXPORT
  DATA = outdir.match
  OUTFILE = "projectdir/data/raw_pulls/lehd_usiris/ecf_interleave_seinunit_t26_2018_usiris_2018q4.dta"
  DBMS = DTA
  REPLACE;
RUN;
**********************************************************************************************************;
**********************************************************************************************************;





**********************************************************************************************************;
**********************************************************************************************************;
* Create SEIN-level file;
PROC SQL;
  CREATE TABLE work.sein_iris AS
  SELECT DISTINCT sein
  FROM outdir.phf_interleave_b_usiris
  WHERE sein IS NOT MISSING;
QUIT;

DATA outdir.match;
  MERGE lehdt13.ecf_interleave_sein_t13 (IN=A) work.sein_iris (IN=B);
  BY sein;
  IF A=1 & B=1 THEN _merge=3;
  IF A=1 & B=0 THEN _merge=1;
  IF A=0 & B=1 THEN _merge=2;
  IF _merge=1 THEN DELETE;
  IF _merge=3 THEN flag_seinunitin_ecf=1;
  IF _merge=2 THEN flag_seinunitin_ecf=0;
  DROP _merge;
RUN;

PROC EXPORT
  DATA = outdir.match
  OUTFILE = "projectdir/data/raw_pulls/lehd_usiris/ecf_interleave_sein_t13_2018_usiris_2018q4.dta"
  DBMS = DTA
  REPLACE;
RUN;


DATA outdir.match;
  MERGE lehdt26.ecf_interleave_sein_t26 (IN=A) work.sein_iris (IN=B);
  BY sein;
  IF A=1 & B=1 THEN _merge=3;
  IF A=1 & B=0 THEN _merge=1;
  IF A=0 & B=1 THEN _merge=2;
  IF _merge=1 THEN DELETE;
  IF _merge=3 THEN flag_seinunitin_ecf=1;
  IF _merge=2 THEN flag_seinunitin_ecf=0;
  DROP _merge;
RUN;

PROC EXPORT
  DATA = outdir.match
  OUTFILE = "projectdir/data/raw_pulls/lehd_usiris/ecf_interleave_sein_t26_2018_usiris_2018q4.dta"
  DBMS = DTA
  REPLACE;
RUN;
**********************************************************************************************************;
**********************************************************************************************************;





